﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonModule;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using ePlanet.ArchiveMange.Models;
using System.Data;
using System.Data.Common;

namespace ePlanet.ArchiveMange.DataAccess
{
    public class DocumentArchiveVolDAL : DataHelper
    {
        public DocumentArchiveVolDAL()
        {
            if (!(this.DBHelper.database is OracleDatabase))
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable GetAllDocumenArchivetData()
        {
            string sql = @"select * from DOCUMENT_ARCHIVE_VOL t ";
            ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable data = new ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    ds.Tables[0].TableName = data.TableName;
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }


        /// <summary>
        /// 根据主键查询数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable GetAllDocumenArchivetDataById(string DATAID)
        {
            string sql = @"select *
from document_archive_vol t where t.DATAID='" + DATAID + "'";
            ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable data = new ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 根据查询条件查询数据
        /// </summary>
        /// <param name="filter">查询条件字符串</param>
        /// <returns></returns>
        public ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable GetAllDocumenArchivetByFilter(string filter)
        {
            string sql = @"select *
from document_archive_vol  ";
            if (!string.IsNullOrEmpty(filter))
            {
                sql += "where 1=1 and " + filter;
            }
            ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable data = new ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 备注：根据DATAID列表取对应档案的详细信息
        /// 时间：2013年1月9日 23:47:41
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public DataTable GetDocumentArchiveDataByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"select rownum rn, 
       a.createuserid,
       a.dataid, 
       a.statusid,
       a.fullfondsnum,
       a.archivenum,
       '' archivesource,
       a.title,
       a.pegecou pagenum,--页数
       '' filenum,  --文件编号
       0 pagefilenum, --件数
       to_char(a.createtime ,'yyyy-mm-dd') publishtime,
       a.createtime,
       '' publisher,--责任者
       a.fileorgid,
       a.mainbodynum,--正本数量
       1 fs,
       '卷' remark,
       b.code_desc as f_keeptime,
       c.code_desc as f_securitytype
  from document_archive_vol a
  left join p_bt_codetable b
    on a.f_keeptime = b.codetableid
  left join p_bt_codetable c
  on a.f_securitytype = c.codetableid
  where a.DATAID  in ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public DataTable GetHandoverDataByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"SELECT A.DATAID,
       '' ARCHIVESOURCE,
       A.CREATETIME,
       A.FILEORGID,
       A.MAINBODYNUM, --正本数量
       B.CODE_DESC     AS F_CARRIERTYPE, --载体类型
       C.CODE_DESC     AS F_KEEPTIME       --保管期限
  FROM DOCUMENT_ARCHIVE_VOL A
  LEFT JOIN P_BT_CODETABLE B
    ON A.F_CARRIERTYPE = B.CODETABLEID
  LEFT JOIN P_BT_CODETABLE C
    ON A.F_KEEPTIME = C.CODETABLEID 
  where a.DATAID  in ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public DataTable GetArchiveDateSpanByIds(IEnumerable<string> idList)
        {
            if (idList == null || idList.Count() == 0)
            {
                return null;
            }
            var ids = string.Join(",", (from id in idList select string.Format("'{0}'", id)).ToArray());
            string sql = string.Format(@"SELECT   MAX(a.createtime) endtime , MIN(a.createtime) starttime
FROM document_archive_vol a 
WHERE a.dataid IN ({0})", ids);
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    return ds.Tables[0];
                }
                return null;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 根据档案类型查询数据
        /// </summary>
        /// <param name="typeId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable GetArchiveByType(string archiveType)
        {
            string sql = @"select *
from document_archive_vol t  where archiveType='" + archiveType + "'";
            ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable data = new ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        /// <summary>
        /// 删除数据(历史数据不能删除)
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        public bool DeleteData(string key)
        {
            //string sql = "delete from document_archive_vol where DATAID=:DATAID";
            //DbCommand cmd = this.DBHelper.database.GetSqlStringCommand(sql);
            //this.DBHelper.database.AddParameter(cmd, "DATAID", DbType.String, ParameterDirection.Input, "DATAID", DataRowVersion.Current, key);
            //int rowcount = this.DBHelper.database.ExecuteNonQuery(cmd);
            //return rowcount > 0 ? true : false;
            return false;
        }

        /// <summary>
        /// 根据档案类型和用户ID查询数据
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable GetTypeArchiveByUserId(string archiveType, string userId)
        {
            string sql = string.Format(@"select *
from document_archive_vol t  where archiveType='{0}' and createuserid='{1}'", archiveType, userId);
            ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable data = new ArchiveMangeModal.DOCUMENT_ARCHIVE_VOLDataTable();
            if (this.DBHelper.database is OracleDatabase)
            {
                DataSet ds = this.DBHelper.ExecuteDataSet(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    data.Merge(ds.Tables[0]);
                }
                return data;
            }
            else
            {
                throw new Exception("只支持Oracle数据库");
            }
        }

        public bool IsSameSecret(List<Guid> dataIDList)
        {
            return true;
        }
    }
}
